package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.bean.ReportDailyAbilityBean;
import com.dudu.bean.ReportDailyBean;

import core.CoreDao;
import core.db.DBConnectionUtil;

/**
 * 报表dao层
 * 处理各种报表查询业务
 * @author jack
 *
 */
public class ReportDao {
	
	private static final Logger log =  core.util.Logger.getLogger(ReportDao.class.getSimpleName());
	
	/**
	 * 查询嘟一下日统计数据
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return
	 */
	public List<ReportDailyBean> queryReportDaily(CoreDao dao, String date, String beginTime, String endTime) {
		log.info("ReportDao.queryReportDaily");	
		Connection con = null; //数据库连接
		List<ReportDailyBean> result = null; //返回值
		try {	
			if(StringUtils.isNotBlank(date) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)){
				con = DBConnectionUtil.getDBConnection("emate1"); //获取数据库连接
				StringBuffer sql = new StringBuffer(512);
				sql.append("	select "); 
				sql.append("		? AS 'date', ");
				sql.append("		'嘟一下' AS 'appName', "); 
				sql.append("		'0' AS 'newUser', "); 
				sql.append("		t1.*, "); 
				sql.append("		t2.* "); 
				sql.append("	FROM  "); 
				sql.append("		( ");
				sql.append("			SELECT  "); 
				sql.append("				sum(i.duration) AS 'totalAmount',  "); 
				sql.append("				COUNT(DISTINCT(i.user_identify)) AS 'activeUser', COUNT(DISTINCT(i.cust_account)) AS 'activeCompany' "); 
				sql.append("			FROM t_meet_info i  "); 
				sql.append("			WHERE  "); 
				sql.append("				i.app_key='99653B0C7EC6897C2F426C0E13A9D66C'  "); 
				sql.append("				AND i.create_time > ? AND i.create_time < ? ");
				sql.append("		) t1, "); 
				sql.append("		( ");
				sql.append("			SELECT "); 
				sql.append("				count(*) AS 'newCompany'  "); 
				sql.append("			FROM t_account_extend t  "); 
				sql.append("			WHERE  "); 
				sql.append("				t.url_ticket LIKE '%dyx%' "); 
				sql.append("				AND t.create_time > ? AND t.create_time < ? ");
				sql.append("		) t2 "); 			
				log.info("queryReportDaily="+sql);
				List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { date, beginTime, endTime, beginTime, endTime });
				if (list != null) {
					result = dao.covertToBean(list, ReportDailyBean.class);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;		
	}
	
	/**
	 * 通道日统计
	 * @param dao
	 * @param beginTime
	 * @param endTime
	 * @return
	 */
	public List<ReportDailyAbilityBean> queryReportDailyAbility(CoreDao dao, String date, String beginTime, String endTime) {
		log.info("ReportDao.queryReportDailyAbility");	
		Connection con = null; //数据库连接
		List<ReportDailyAbilityBean> result = null; //返回值
		try {	
			if(StringUtils.isNotBlank(date) && StringUtils.isNotBlank(beginTime) && StringUtils.isNotBlank(endTime)){
				con = DBConnectionUtil.getDBConnection("emate1"); //获取数据库连接
				StringBuffer sql = new StringBuffer(512);
				sql.append("	select "); 
				sql.append("		? AS 'date', ");
				sql.append("        CASE i.ability_type ");
				sql.append("        	WHEN 'TianYiVoice' THEN '天亿' ");
				sql.append("			WHEN 'TianYiAudeo' THEN '天亿' ");
			    sql.append("			WHEN 'RongLianVoice' THEN '容联' ");
			    sql.append("			WHEN 'RongLianAudeo' THEN '容联' ");
				sql.append("			WHEN 'Ivr_RongL' THEN '容联' ");
				sql.append("			ELSE 'IMS' ");
				sql.append("		END AS 'ability', ");
				sql.append("		i.ability_type, ");
				sql.append("		COUNT(i.id) AS 'totalAmount', "); 
				sql.append("		SUM(i.duration) AS 'totalDurration' "); 
				sql.append("	FROM  "); 
				sql.append("		t_meet_info i ");
				sql.append("	WHERE  "); 
				sql.append("		i.create_time > ? AND i.create_time < ? ");	
				sql.append("		GROUP BY i.ability_type ");	
				log.info("queryReportDailyAbility="+sql);
				List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { date, beginTime, endTime });
				if (list != null) {
					result = dao.covertToBean(list, ReportDailyAbilityBean.class);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;		
	}
}
